Create JSON structure using SQL In my new project I came across a new requirement. I have to create JSON structure using SQL. Ya it is crazy, but it is a good experience. I will explain it now… I am having three tables such as ATab, BTab, and CTab as followes
CREATE TABLE IF NOT EXISTS ATab (
id int(8) unsigned NOT NULL AUTO_INCREMENT,
oid int(8) unsigned NOT NULL,
type int(1) unsigned NOT NULL ,
parent int(8) unsigned NOT NULL ,
ctime int(8) NOT NULL ,
utime int(11) DEFAULT NULL ,
PRIMARY KEY (id),
KEY re (oid),
KEY re_type (type),
KEY rg_parent (parent)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS BTab (
pid int(8) unsigned NOT NULL AUTO_INCREMENT,
ptype int(1) DEFAULT NULL ,
pname varchar(128) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (pid),
KEY ptype (ptype)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS CTab (
cid int(11) unsigned NOT NULL AUTO_INCREMENT,
id int(11) DEFAULT NULL,
mvl text CHARACTER SET utf8,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample entries:
INSERT INTO ATab (id, oid, type, parent, ctime, utime) VALUES
(1, 1, 1, 1, 1383740369, 1383740369),
(2, 1, 2, 1, 1383740379, 1383740379),
(3, 2, 1, 2, 1383740389, 1383740399),
(4, 2, 2, 2, 1383740479, 1383740479);
INSERT INTO BTab (pid, ptype, pname) VALUES
(1, 1, 'parent1'),
(2, 2, 'parent2');
INSERT INTO CTab (cid, id, mvl) VALUES
(1, 1, '{\r\n "test": "test json data "\r\n}'),
(2, 2, '{\r\n "sample": "sample json data"\r\n}');
BTab contains the parent entries. ATab contains the child entries for the entries in BTab. There will be multiple childs for parents. CTab contains data related to ATab (CTab.id = ATab.id). There will not be entries in CTab for all entries in the ATab(So I used LEFT OUTER JOIN). Now the output should be in the following format:
{ "items": [ { "t1": { "id": 1, "oid": 1, "ty": 1, "p": 1, "ct": 1383740369, "ut": 1383740369, "mvl": { "test": "test json data " } } }, { "t2": { "id": 2, "oid": 1, "ty": 2, "p": 1, "ct": 1383740379, "ut": 1383740379, "mvl": { "sample": "sample json data" } } } ] } So here is the crazy query:
SELECT CONCAT( "{\"items\":[", CONVERT( GROUP_CONCAT( "{\"t", TYPE , "\":{\"id\":", ATab.id, ",\"oid\":", oid, ",\"ty\":", TYPE , ",\"p\":", parent, ", \"ct\":", ctime, ", \"ut\":", utime, " , \"mvl\":", IFNULL( CTab.mvl, "{}" ) , "}}" ORDER BY utime DESC ) USING utf8 ) , "]}" ) AS jsn FROM ATab JOIN BTab LEFT OUTER JOIN CTab ON CTab.id = ATab.id WHERE oid =1 AND ATab.parent = BTab.pid AND ATab.parent IN ( 1 ) GROUP BY ATab.parent LIMIT 0 , 500;
For people who interested, Sqlfiddle URL http://sqlfiddle.com/#!2/3564f/1